import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report, plot_roc_curve, roc_curve, auc, roc_auc_score

# 为逻辑回归画ROC曲线图
def roc_for_logic(clf=None, x_test=None, y_test=None):
    #创建画布
    fig, ax = plt.subplots(figsize=(12,10))
    lr_roc = plot_roc_curve(estimator=clf, X=x_test, 
                            y=y_test, ax=ax, linewidth=1)
    #更改图例字体大小
    ax.legend(fontsize=12)
    #绘制对角线
    ax.plot([0,1],[0,1],linestyle='--',color='grey')
    #显示绘制的ROC曲线
    plt.show()

# 逻辑回归预测模型
def logic():
    data = pd.read_excel(r'./data/表单1.xlsx')
    # 2. 数据基本处理
    # 2.1 缺失值处理 —— 将?替换为NaN，然后删除NaN
    data = data.replace(to_replace="?", value=np.NaN)
    data = data.dropna()
    # 2.2 确定特征值(第一列不是特征值)和目标值(最后一列)以便分割数据
    x = data.iloc[:, 1:14]
    y = data.iloc[:, 14]

    # 2.3 分割数据
    x_train, x_test, y_train, y_test = train_test_split(x, y, random_state=22, test_size=0.2)
    # 4. 机器学习 —— 逻辑回归
    estimator = LogisticRegression()
    estimator.fit(x_train, y_train)
    # 保存模型
    # joblib.dump(estimator, r'./save/logic.pkl')

    # 5. 模型评估
    y_predict = estimator.predict(x_test)    # 预测值
    score = estimator.score(x_test, y_test)  # 准确率
    
    # 各参数输出
    report = classification_report(y_test, y_predict)
    print("预测值：\n", y_predict)
    print("准确率：", score)
    print(report)
    print("AUC指标:", roc_auc_score(y_test, y_predict))
    roc_for_logic(clf=estimator,x_test=x_test,y_test=y_test)

# 关联规则
def association():
    from mlxtend.preprocessing import TransactionEncoder
    from mlxtend.frequent_patterns import apriori
    from mlxtend.frequent_patterns import association_rules

    # 读取数据
    df = pd.read_excel(r'./data/附件.xlsx',header=None)
    data = df.replace(to_replace="?", value=np.NaN)
    data = data.dropna()
    data = data.iloc[1:,1:]
    df_arr = np.array(data)

    # 转换为算法可接受模型（布尔值）
    te = TransactionEncoder()
    df_tf = te.fit_transform(df_arr)
    df = pd.DataFrame(df_tf, columns=te.columns_)

    #设置支持度求频繁项集
    frequent_itemsets = apriori(df,min_support=0.4,use_colnames= True)
    #求关联规则,设置最小置信度为0.15
    rules = association_rules(frequent_itemsets,metric = 'confidence',min_threshold = 0.15)
    #设置最小提升度
    rules = rules.drop(rules[rules.lift < 0.1].index)
    #设置标题索引并打印结果
    rules.rename(columns = {'antecedents':'from','consequents':'to','support':'sup','confidence':'conf'},inplace = True)
    rules = rules[['from','to','sup','conf','lift']]
    print(rules)
    #rules为Dataframe格式，可根据自身需求存入文件

# 线性拟合
def liner():
    from sklearn.linear_model import LinearRegression #导入机器学习库中的线性回归模块
    
    # 读取数据
    df = pd.read_excel(r'./data/表单2/表单2.xlsx')
    r_lis = []  # 用来存放r方值
    attribute_lis = np.array(['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2', 'SO2'])
    data_x = df.iloc[:, 1:15]
    x_arr = np.array(data_x)
    # 循环判断每一列成分类型
    for i in range(16,30):
        data_y = df.iloc[:,i]
        y_arr = np.array(data_y)
        regr = LinearRegression() #创建线性回归模型，参数默认
        regr.fit(x_arr, y_arr)#拟合数据

        # 各参数输出
        r_lis.append(regr.score(x_arr,y_arr))
        print("{}多项式系数:{}".format(i-15, regr.coef_))
        print("截距:", regr.intercept_)
        print("R方值:", regr.score(x_arr,y_arr))
        print("-------------------------------------")

    # 画图 r方值与化学成分
    plt.plot(attribute_lis, np.array(r_lis), color='green', linewidth=1, linestyle='-.')
    plt.show()


# 创建更新的excel
def create_excel():
    df = pd.read_excel(r'./data/表单2.xlsx')
    data = df.iloc[:, 1:15]
    writer = pd.ExcelWriter(r'./data/表单2更新成分.xlsx')		# 写入Excel文件
    data.to_excel(writer, 'Sheet1', float_format='%.5f')		# ‘page_1’是写入excel的sheet名
    writer.save()
    writer.close()

# 更新未分化前的某些元素的成分含量
def update():
    from sklearn.linear_model import LinearRegression #导入机器学习库中的线性回归模块

    # 读取数据
    df = pd.read_excel(r'./data/表单2/表单2.xlsx')
    df_x = df.iloc[:, 1:15]
    df_y = df.iloc[:, 16]

    # 这边需要挑选更新的化学成分
    df_y = df.iloc[:,25]
    regr = LinearRegression() #创建线性回归模型，参数默认
    regr.fit(df_x, df_y)#拟合数据

    # 需要将预测数据输出
    data = pd.read_excel(r'./data/表单2更新成分.xlsx')
    result = regr.predict(data.iloc[:,1:15])
    datafram = pd.DataFrame(result)
    writer = pd.ExcelWriter(r'./data/更新成分.xlsx')		# 写入Excel文件
    datafram.to_excel(writer, 'Sheet1', float_format='%.5f')		# ‘page_1’是写入excel的sheet名
    writer.save()
    writer.close()



if __name__=='__main__':
    logic()
    # association()
    # liner()
    # update()